
clear
set more off
program drop _all

local owndir = "" // put your path here

global dirdata = "`owndir'/Raw_Data"
global dirin = "`owndir'/Dta"
global dirout = "`owndir'/Output"


*** 1. SKT Inflow-Outflow

** Monthly
local ww 201911_W 201911_H
foreach i of local ww {
use "$dirdata/`i'.dta",clear

gen dst_code=substr(dst_sgng_code,1,2)
gen src_code=substr(src_sgng_code,1,2)
keep if dst_code=="11"&src_code=="11"
destring in_ingu,replace
rename in_ingu number

rename src_sgng_name origin
rename dst_sgng_name destination

replace origin="Dobong-gu" if origin=="도봉구"
replace origin="Dongdaemun-gu" if origin=="동대문구"
replace origin="Dongjak-gu" if origin=="동작구"
replace origin="Eunpyeong-gu" if origin=="은평구"
replace origin="Gangbuk-gu" if origin=="강북구"
replace origin="Gangdong-gu" if origin=="강동구"
replace origin="Gangnam-gu" if origin=="강남구"
replace origin="Gangseo-gu" if origin=="강서구"
replace origin="Geumcheon-gu" if origin=="금천구"
replace origin="Guro-gu" if origin=="구로구"
replace origin="Gwanak-gu" if origin=="관악구"
replace origin="Gwangjin-gu" if origin=="광진구"
replace origin="Jongno-gu" if origin=="종로구"
replace origin="Jung-gu" if origin=="중구"
replace origin="Jungnang-gu" if origin=="중랑구"
replace origin="Mapo-gu" if origin=="마포구"
replace origin="Nowon-gu" if origin=="노원구"
replace origin="Seocho-gu" if origin=="서초구"
replace origin="Seodaemun-gu" if origin=="서대문구"
replace origin="Seongbuk-gu" if origin=="성북구"
replace origin="Seongdong-gu" if origin=="성동구"
replace origin="Songpa-gu" if origin=="송파구"
replace origin="Yangcheon-gu" if origin=="양천구"
replace origin="Yeongdeungpo-gu" if origin=="영등포구"
replace origin="Yongsan-gu" if origin=="용산구"

replace destination="Dobong-gu" if destination=="도봉구"
replace destination="Dongdaemun-gu" if destination=="동대문구"
replace destination="Dongjak-gu" if destination=="동작구"
replace destination="Eunpyeong-gu" if destination=="은평구"
replace destination="Gangbuk-gu" if destination=="강북구"
replace destination="Gangdong-gu" if destination=="강동구"
replace destination="Gangnam-gu" if destination=="강남구"
replace destination="Gangseo-gu" if destination=="강서구"
replace destination="Geumcheon-gu" if destination=="금천구"
replace destination="Guro-gu" if destination=="구로구"
replace destination="Gwanak-gu" if destination=="관악구"
replace destination="Gwangjin-gu" if destination=="광진구"
replace destination="Jongno-gu" if destination=="종로구"
replace destination="Jung-gu" if destination=="중구"
replace destination="Jungnang-gu" if destination=="중랑구"
replace destination="Mapo-gu" if destination=="마포구"
replace destination="Nowon-gu" if destination=="노원구"
replace destination="Seocho-gu" if destination=="서초구"
replace destination="Seodaemun-gu" if destination=="서대문구"
replace destination="Seongbuk-gu" if destination=="성북구"
replace destination="Seongdong-gu" if destination=="성동구"
replace destination="Songpa-gu" if destination=="송파구"
replace destination="Yangcheon-gu" if destination=="양천구"
replace destination="Yeongdeungpo-gu" if destination=="영등포구"
replace destination="Yongsan-gu" if destination=="용산구"

keep origin destination number

save "$dirin/`i'_clean.dta",replace
}


** Daily

use "$dirdata/SEOUL_TO_SEOUL_202001.dta",clear
local months "02 03 04 05"
foreach x of local months {
append using "$dirdata/SEOUL_TO_SEOUL_2020`x'.dta"
}
distinct

rename std_ymd date
drop day
tostring date,replace
gen year=substr(date,1,4)
gen month=substr(date,5,2)
gen day=substr(date,7,2)
destring year,replace
destring month,replace
destring day,replace
gen all_dates=mdy(month,day,year)
format all_dates %td
drop date year month day

rename inflow_nm origin
replace origin="Dobong-gu" if origin=="도봉구"
replace origin="Dongdaemun-gu" if origin=="동대문구"
replace origin="Dongjak-gu" if origin=="동작구"
replace origin="Eunpyeong-gu" if origin=="은평구"
replace origin="Gangbuk-gu" if origin=="강북구"
replace origin="Gangdong-gu" if origin=="강동구"
replace origin="Gangnam-gu" if origin=="강남구"
replace origin="Gangseo-gu" if origin=="강서구"
replace origin="Geumcheon-gu" if origin=="금천구"
replace origin="Guro-gu" if origin=="구로구"
replace origin="Gwanak-gu" if origin=="관악구"
replace origin="Gwangjin-gu" if origin=="광진구"
replace origin="Jongno-gu" if origin=="종로구"
replace origin="Jung-gu" if origin=="중구"
replace origin="Jungnang-gu" if origin=="중랑구"
replace origin="Mapo-gu" if origin=="마포구"
replace origin="Nowon-gu" if origin=="노원구"
replace origin="Seocho-gu" if origin=="서초구"
replace origin="Seodaemun-gu" if origin=="서대문구"
replace origin="Seongbuk-gu" if origin=="성북구"
replace origin="Seongdong-gu" if origin=="성동구"
replace origin="Songpa-gu" if origin=="송파구"
replace origin="Yangcheon-gu" if origin=="양천구"
replace origin="Yeongdeungpo-gu" if origin=="영등포구"
replace origin="Yongsan-gu" if origin=="용산구"

rename sgng_nm destination
replace destination="Dobong-gu" if destination=="도봉구"
replace destination="Dongdaemun-gu" if destination=="동대문구"
replace destination="Dongjak-gu" if destination=="동작구"
replace destination="Eunpyeong-gu" if destination=="은평구"
replace destination="Gangbuk-gu" if destination=="강북구"
replace destination="Gangdong-gu" if destination=="강동구"
replace destination="Gangnam-gu" if destination=="강남구"
replace destination="Gangseo-gu" if destination=="강서구"
replace destination="Geumcheon-gu" if destination=="금천구"
replace destination="Guro-gu" if destination=="구로구"
replace destination="Gwanak-gu" if destination=="관악구"
replace destination="Gwangjin-gu" if destination=="광진구"
replace destination="Jongno-gu" if destination=="종로구"
replace destination="Jung-gu" if destination=="중구"
replace destination="Jungnang-gu" if destination=="중랑구"
replace destination="Mapo-gu" if destination=="마포구"
replace destination="Nowon-gu" if destination=="노원구"
replace destination="Seocho-gu" if destination=="서초구"
replace destination="Seodaemun-gu" if destination=="서대문구"
replace destination="Seongbuk-gu" if destination=="성북구"
replace destination="Seongdong-gu" if destination=="성동구"
replace destination="Songpa-gu" if destination=="송파구"
replace destination="Yangcheon-gu" if destination=="양천구"
replace destination="Yeongdeungpo-gu" if destination=="영등포구"
replace destination="Yongsan-gu" if destination=="용산구"

gen flow1=m_10+m_20+m_30+m_40+m_50+w_10+w_20+w_30+w_40+w_50
gen flow2=m_60+m_70+w_60+w_70

keep all_dates origin destination flow1 flow2
reshape long flow, i (all_dates origin destination) j (age_group)

save "$dirin/flows_ij_by_age.dta",replace



*** 2. CASES BY DISTRICTS

use "$dirdata/covid19_Seoul_asof_0531.dta",clear

gen month=substr(확진일,1,1)
gen day=substr(확진일,3,2)
destring month,replace
destring day,replace
drop 확진일

replace 거주지="Dobong-gu" if 거주지=="도봉구"
replace 거주지="Dongdaemun-gu" if 거주지=="동대문구"
replace 거주지="Dongjak-gu" if 거주지=="동작구"
replace 거주지="Eunpyeong-gu" if 거주지=="은평구"
replace 거주지="Eunpyeong-gu" if 거주지=="은평"
replace 거주지="Gangbuk-gu" if 거주지=="강북구"
replace 거주지="Gangdong-gu" if 거주지=="강동구"
replace 거주지="Gangnam-gu" if 거주지=="강남구"
replace 거주지="Gangseo-gu" if 거주지=="강서구"
replace 거주지="Geumcheon-gu" if 거주지=="금천구"
replace 거주지="Guro-gu" if 거주지=="구로구"
replace 거주지="Gwanak-gu" if 거주지=="관악구"
replace 거주지="Gwangjin-gu" if 거주지=="광진구"
replace 거주지="Jongno-gu" if 거주지=="종로구"
replace 거주지="Jung-gu" if 거주지=="중구"
replace 거주지="Jungnang-gu" if 거주지=="중랑구"
replace 거주지="Mapo-gu" if 거주지=="마포구"
replace 거주지="Nowon-gu" if 거주지=="노원구"
replace 거주지="Seocho-gu" if 거주지=="서초구"
replace 거주지="Seodaemun-gu" if 거주지=="서대문구"
replace 거주지="Seongbuk-gu" if 거주지=="성북구"
replace 거주지="Seongdong-gu" if 거주지=="성동구"
replace 거주지="Songpa-gu" if 거주지=="송파구"
replace 거주지="Yangcheon-gu" if 거주지=="양천구"
replace 거주지="Yeongdeungpo-gu" if 거주지=="영등포구"
replace 거주지="Yongsan-gu" if 거주지=="용산구"
 
rename 연번 number
rename 환자 patient_number
rename 거주지 district
rename 여행력 travel_abroad
rename 접촉력 source
rename 조치사항 hospital

save "$dirin/covid19_Seoul_asof_0531_clean.dta",replace

// calculate cumulative sum

use "$dirin/covid19_Seoul_asof_0531_clean.dta",clear
gen case=1
collapse (sum) case, by (month day)
gen year=2020
gen datevar=mdy(month,day,year)
format datevar %td
tsset datevar 
tsfill, full
replace case=0 if case==.
drop month day
gen month=month(datevar)
gen day=day(datevar)
tsset datevar 
foreach i of numlist 1/14 {
gen Lcase_`i'=l`i'.case
}
egen cum_cases_14=rowtotal(Lcase_*)
keep cum_cases_14 month day
rename cum_cases_14 cum_cases_total_14
save "$dirin/covid19_Seoul_cum_cases_14.dta",replace

use "$dirin/covid19_Seoul_asof_0531_clean.dta",clear
gen case=1
collapse (sum) case, by (month day district)
gen year=2020
gen datevar=mdy(month,day,year)
format datevar %td
egen d=group(district)
tsset d datevar 
tsfill, full
replace case=0 if case==.
drop month day
gen month=month(datevar)
gen day=day(datevar)
drop district
merge m:1 d using "$dirdata/district_list.dta"
keep if _merge==3
tsset d datevar 
foreach i of numlist 1/14 {
gen Lcase_`i'=l`i'.case
}
egen cum_cases_14=rowtotal(Lcase_*)
su cum_cases_14,d
keep cum_cases_14 month day district
save "$dirin/covid19_Seoul_cum_cases_14_by_district.dta",replace


*** 3. VISITS BY DISTRICTS

// calculate cumulative sum
use "$dirdata/datasets_PatientRoute.dta",clear
rename city district
keep if province=="Seoul"
keep date district
gen month=substr(date,6,2)
gen day=substr(date,9,2)
destring month,replace
destring day,replace
gen visit=1
collapse (sum) visit, by (month day district)
gen year=2020
gen datevar=mdy(month,day,year)
format datevar %td
egen d=group(district)
tsset d datevar 
tsfill, full
replace visit=0 if visit==.
drop month day
gen month=month(datevar)
gen day=day(datevar)
drop district
merge m:1 d using "$dirdata/district_list.dta"
keep if _merge==3
tsset d datevar 
foreach i of numlist 1/14 {
gen Lvisit_`i'=l`i'.visit
}
egen cum_visits_14=rowtotal( Lvisit_*)
su cum_visits_14,d
keep cum_visits_14 month day district
save "$dirin/covid19_Seoul_cum_visits_14_by_district.dta",replace



*** 4. Seoul Survey

use "$dirdata/Seoul_Survey.dta", clear

gen district=""
replace district="Dobong-gu" if de6==320
replace district="Dongdaemun-gu" if de6==230
replace district="Dongjak-gu" if de6==590
replace district="Eunpyeong-gu" if de6==380
replace district="Gangbuk-gu" if de6==305
replace district="Gangdong-gu" if de6==740
replace district="Gangnam-gu" if de6==680
replace district="Gangseo-gu" if de6==500
replace district="Geumcheon-gu" if de6==545
replace district="Guro-gu" if de6==530
replace district="Gwanak-gu" if de6==620
replace district="Gwangjin-gu" if de6==215
replace district="Jongno-gu" if de6==110
replace district="Jung-gu" if de6==140
replace district="Jungnang-gu" if de6==260
replace district="Mapo-gu" if de6==440
replace district="Nowon-gu" if de6==350
replace district="Seocho-gu" if de6==650
replace district="Seodaemun-gu" if de6==410
replace district="Seongbuk-gu" if de6==290
replace district="Seongdong-gu" if de6==200
replace district="Songpa-gu" if de6==710
replace district="Yangcheon-gu" if de6==470
replace district="Yeongdeungpo-gu" if de6==560
replace district="Yongsan-gu" if de6==170
tab district
distinct district

// 문33. 귀하는 통근이나 통학을 하십니까?
rename aq33 commute
// 문33-1. 귀하는 어디로 통근 또는 통학하십니까?
* answer if your answer to previous question is 1 or 2 (NOT 3)
rename aq33_1 com_pattern
// 문33-1. 다른 구 다른 시도
* answer if your answer to previous question is 3 or 4 (NOT 1,2)
replace aq33_1_3="" if aq33_1_3=="#NULL!"
destring aq33_1_3,replace
gen destination=""
replace destination="Dobong-gu" if aq33_1_3==110
replace destination="Dongdaemun-gu" if aq33_1_3==111
replace destination="Dongjak-gu" if aq33_1_3==112
replace destination="Eunpyeong-gu" if aq33_1_3==122
replace destination="Gangbuk-gu" if aq33_1_3==103
replace destination="Gangdong-gu" if aq33_1_3==102
replace destination="Gangnam-gu" if aq33_1_3==101
replace destination="Gangseo-gu" if aq33_1_3==104
replace destination="Geumcheon-gu" if aq33_1_3==108
replace destination="Guro-gu" if aq33_1_3==107
replace destination="Gwanak-gu" if aq33_1_3==105
replace destination="Gwangjin-gu" if aq33_1_3==106
replace destination="Jongno-gu" if aq33_1_3==123
replace destination="Jung-gu" if aq33_1_3==124
replace destination="Jungnang-gu" if aq33_1_3==125
replace destination="Mapo-gu" if aq33_1_3==113
replace destination="Nowon-gu" if aq33_1_3==109
replace destination="Seocho-gu" if aq33_1_3==115
replace destination="Seodaemun-gu" if aq33_1_3==114
replace destination="Seongbuk-gu" if aq33_1_3==117
replace destination="Seongdong-gu" if aq33_1_3==116
replace destination="Songpa-gu" if aq33_1_3==118
replace destination="Yangcheon-gu" if aq33_1_3==119
replace destination="Yeongdeungpo-gu" if aq33_1_3==120
replace destination="Yongsan-gu" if aq33_1_3==121
replace destination="Outside Seoul" if aq33_1_3>=200
replace destination="Not commuting" if aq33_1_3==.&commute==3
replace destination="Commuting within district" if aq33_1_3==.&commute!=3
tab destination
distinct destination

save "$dirin/Seoul_Survey_clean.dta",replace
